Phân tích tình hình kinh doanh của 1 cửa hàng¶

Dữ liệu được lấy từ 1 cửa hàng thực tế. Tuy nhiên để đảm bảo việc bảo mật dữ liệu 1 số thông tin đã bị mã hóa và thay đổi

In [1]:
import pyodbc
import numpy as np
import pandas as pd
import os
import matplotlib.pyplot as plt
import plotly.express as px
from plotly.subplots import make_subplots
import plotly.graph_objects as go
import plotly.offline as plot
import ipywidgets as widgets
from jupyter_dash import JupyterDash
from dash import Dash, dcc, html, Input, Output


import warnings
warnings.filterwarnings("ignore")

TASK1 :Đẩy dữ liệu vào SQL Server¶

Dữ liệu được lấy từ web bán hàng, có thể lấy dữ liệu theo ngày hoặc theo tháng. Dữ liệu được lấy xuống là 1 data frame gồm rất nhiều trường thông tin. Chúng ta sẽ xử lý để tách làm 3 bảng và đẩy vào SQL server để lưu trữ và tính toán

1.1 Khởi tạo kết nối¶

In [2]:
SVNAME = 'DESKTOP-V57CC5Q\HOCSQL'
DBNAME = 'QLBHTKL'
username = '' 
password = ''
In [3]:
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+SVNAME+';DATABASE='+DBNAME+';UID='+username+';PWD='+ password)
cursor = conn.cursor()

1.2 Gộp dữ liệu từ nhiều file¶

In [4]:
input_file_path = "D:/courses.funix/CV/Data/"
output_file_path = "D:/courses.funix/CV/"
In [7]:
excel_file_list = os.listdir(input_file_path)
In [8]:
df = pd.DataFrame()
In [9]:
for excel_files in excel_file_list:
    if excel_files.endswith(".xlsx"):
        df1 = pd.read_excel(input_file_path+excel_files)
        df = df.append(df1)
df.to_excel(output_file_path+"Data_file.xlsx")
In [7]:
df = pd.read_excel('Data_file.xlsx')
In [8]:
df.head(5)
Out[8]:
STT MÃ CN CHI NHÁNH NGÀY HĐ SỐ HĐ MÃ KHÁCH 2 TÊN KHÁCH MÃ HÀNG TÊN HÀNG GIỚI TÍNH ... THÀNH TIỀN BÁN THÀNH TIỀN NTL MÔ TẢ HẠNG KHÁCH CHƯƠNG TRÌNH KHUYẾN MẠI SubClass1 THUỘC TÍNH 1 NĂM SX MÃ HÀNG1 NHÂN VIÊN
0 1 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000175 1160637 Trần Thị Hằng I7JCK601F-015 Áo khoác lông vũ Nam 3 lớp cổ 5cm dáng ngắn I7... Nam ... 0.0 387000.0 NaN AppMember NaN cổ 5cm 3 lớp Đông 2019 I7JCK601F NaN
1 2 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000176 907256 Chị Oanh I9COA502H-005 Áo khoác Nữ dáng dài I9COA502H Nữ ... 0.0 474000.0 NaN AppMember NaN có mũ 2 lớp mỏng Đông 2020 I9COA502H NaN
2 3 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000177 163424 Nguyễn Thị Hà I7SWT601F-001 Áo len Nam cổ tròn I7SWT601F Nam ... 0.0 147000.0 NaN AppMember NaN cổ tròn Len Đông 2019 I7SWT601F NaN
3 4 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000178 1834386 Nguyễn Minh Nguyệt M9ACP500H-002 Quần Leggings lửng Nữ M9ACP500H Nữ ... 0.0 45000.0 NaN AppMember NaN NaN NaN Đông 2020 M9ACP500H NaN
4 5 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000179 1984934 Chị Hà C9SML008D-002 Quần Leggings Nữ C9SML-008D Nữ ... 0.0 87000.0 NaN AppMember NaN NaN NaN Đông 2018 C9SML-008D NaN

5 rows × 33 columns

In [9]:
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 115050 entries, 0 to 115049
Data columns (total 33 columns):
 #   Column                   Non-Null Count   Dtype         
---  ------                   --------------   -----         
 0   STT                      115050 non-null  int64         
 1   MÃ CN                    115050 non-null  int64         
 2   CHI NHÁNH                115050 non-null  object        
 3   NGÀY HĐ                  115050 non-null  datetime64[ns]
 4   SỐ HĐ                    115050 non-null  object        
 5   MÃ KHÁCH 2               115050 non-null  int64         
 6   TÊN KHÁCH                115050 non-null  object        
 7   MÃ HÀNG                  115050 non-null  object        
 8   TÊN HÀNG                 115050 non-null  object        
 9   GIỚI TÍNH                76296 non-null   object        
 10  NHÓM HÀNG                115050 non-null  object        
 11  THƯƠNG HIỆU              109602 non-null  object        
 12  KÍCH CỠ                  93469 non-null   object        
 13  MÀU SẮC                  82452 non-null   object        
 14  ĐƠN VỊ                   115040 non-null  object        
 15  DÒNG HÀNG                115006 non-null  object        
 16  KẾT CẤU                  114977 non-null  object        
 17  BỘ SƯU TẬP               81575 non-null   object        
 18  SL BÁN                   115050 non-null  int64         
 19  SL TRẢ LẠI               115050 non-null  int64         
 20  GIÁ                      115050 non-null  int64         
 21  GIẢM GIÁ                 115050 non-null  float64       
 22  G.TRÊN HĐ                115050 non-null  float64       
 23  THÀNH TIỀN BÁN           115050 non-null  float64       
 24  THÀNH TIỀN NTL           115050 non-null  float64       
 25  MÔ TẢ                    23255 non-null   object        
 26  HẠNG KHÁCH               114445 non-null  object        
 27  CHƯƠNG TRÌNH KHUYẾN MẠI  69094 non-null   object        
 28  SubClass1                46658 non-null   object        
 29  THUỘC TÍNH 1             47907 non-null   object        
 30  NĂM SX                   80109 non-null   object        
 31  MÃ HÀNG1                 114960 non-null  object        
 32  NHÂN VIÊN                5283 non-null    object        
dtypes: datetime64[ns](1), float64(4), int64(6), object(22)
memory usage: 29.0+ MB

1.3 Thiết kế 1 lược đồ CSDL đơn giản để lưu data vào trong SQL¶

image.png

1.4 Đẩy dữ liệu vào SQL Server¶

In [10]:
columns_khachhang = ['MÃ KHÁCH 2','TÊN KHÁCH']
In [11]:
columns_hanghoa = ['MÃ HÀNG','TÊN HÀNG','GIỚI TÍNH','NHÓM HÀNG','THƯƠNG HIỆU','KÍCH CỠ','MÀU SẮC','ĐƠN VỊ','DÒNG HÀNG','KẾT CẤU','BỘ SƯU TẬP']
In [12]:
columns_hoadon = ['MÃ CN','CHI NHÁNH','NGÀY HĐ','SỐ HĐ','MÃ KHÁCH 2','MÃ HÀNG','SL BÁN','SL TRẢ LẠI','GIÁ','GIẢM GIÁ','G.TRÊN HĐ','THÀNH TIỀN BÁN','THÀNH TIỀN NTL','MÔ TẢ','HẠNG KHÁCH','CHƯƠNG TRÌNH KHUYẾN MẠI','SubClass1','THUỘC TÍNH 1','NĂM SX','MÃ HÀNG1','NHÂN VIÊN']
In [13]:
df_khachhang = df[columns_khachhang].drop_duplicates()
In [14]:
df_khachhang
Out[14]:
MÃ KHÁCH 2 TÊN KHÁCH
0 1160637 Trần Thị Hằng
1 907256 Chị Oanh
2 163424 Nguyễn Thị Hà
3 1834386 Nguyễn Minh Nguyệt
4 1984934 Chị Hà
... ... ...
114986 2497284 Chị Hiền
114989 2497294 Mr Trung
114996 2497332 Ms Liên
115005 2497497 C,Quỳnh
115018 2497637 Ms Hương

11704 rows × 2 columns

In [15]:
df_hanghoa = df[columns_hanghoa].drop_duplicates()
In [16]:
df_hoadon = df[columns_hoadon]

a: Đẩy dữ liệu khách hàng vào table khách hàng¶

In [17]:
records_khachhang = df_khachhang.values.tolist()
In [18]:
cursor.execute('''
        DROP TABLE IF EXISTS khachhang
        CREATE TABLE khachhang (
        [MÃ KHÁCH 2] int primary key,
        [TÊN KHÁCH] nvarchar(50))
               ''')
conn.commit()
In [19]:
sql_insert_khachhang = '''
    INSERT INTO khachhang 
    VALUES (?, ?)
'''
In [20]:
cursor.executemany(sql_insert_khachhang, records_khachhang)
cursor.commit();
In [21]:
sql_query = """
SELECT TOP 10 *
FROM khachhang
"""
In [22]:
df_check = pd.read_sql(sql_query, conn)
df_check
Out[22]:
MÃ KHÁCH 2 TÊN KHÁCH
0 125517 Hoàng Văn Tùng
1 126268 Nguyễn THu huyền
2 126363 Nguyên Thị Nga
3 126405 Cô Nhung
4 126568 Đỗ Thị Tiến Dung
5 126781 Phạm Nhật Lệ
6 127020 Chị Lê Diệu Linh
7 127365 05
8 127463 Nv Đỗ Thị Thu Trang
9 128042 Phạm Thị Thu Nga

b: Đẩy dữ liệu hàng hóa vào table hàng hóa¶

In [23]:
records_hanghoa = df_hanghoa.fillna(value=0).values.tolist()
In [24]:
cursor.execute('''
        DROP TABLE IF EXISTS hanghoa
        CREATE TABLE hanghoa (
        [MÃ HÀNG] nvarchar(50) primary key,
        [TÊN HÀNG] nvarchar(100),
        [GIỚI TÍNH] nvarchar(20),
        [NHÓM HÀNG] nvarchar(100),
        [THƯƠNG HIỆU] nvarchar(100),
        [KÍCH CỠ] nvarchar(50),
        [MÀU SẮC] nvarchar(50),
        [ĐƠN VỊ] nvarchar(20),
        [DÒNG HÀNG] nvarchar(100),
        [KẾT CẤU] nvarchar(100),
        [BỘ SƯU TẬP] nvarchar(100))
               ''')
conn.commit()
In [25]:
sql_insert_hanghoa = '''
    INSERT INTO hanghoa 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
In [26]:
cursor.executemany(sql_insert_hanghoa, records_hanghoa)
cursor.commit();
In [27]:
sql_query = """
SELECT TOP 10 *
FROM hanghoa
"""
In [28]:
df_check = pd.read_sql(sql_query, conn)
df_check
Out[28]:
MÃ HÀNG TÊN HÀNG GIỚI TÍNH NHÓM HÀNG THƯƠNG HIỆU KÍCH CỠ MÀU SẮC ĐƠN VỊ DÒNG HÀNG KẾT CẤU BỘ SƯU TẬP
0 2733217190301 Bộ Jumpsuits Bé trai ngắn tay BLA 27332171903 ... Bé trai Thời trang NOW BALABALA Size 73 Vàng Bộ Bộ Bộ Jumpsuits Xuân Hè
1 3588981242178-1222 Nước hoa TOKYOLIFE OCEAN EDP 20ml Nam Hàng tiêu dùng nhanh TOKYOLIFE 20ml 0 Chai Nước hoa Nước hoa Bốn mùa
2 3588981242185-1124 Nước hoa TOKYOLIFE NIGHT SKY EDP Nam Hàng tiêu dùng nhanh TOKYOLIFE 24ml 0 Chai Nước hoa Nước hoa Thu đông 2021
3 3588981242185-1222 Nước hoa TOKYOLIFE NIGHT SKY EDP 20ml Nam Hàng tiêu dùng nhanh TOKYOLIFE 20ml 0 Chai Nước hoa Nước hoa Bốn mùa
4 3588981242192-1124 Nước hoa TOKYOLIFE UNIVERSE EDP Nam Hàng tiêu dùng nhanh TOKYOLIFE 24ml 0 Chai Nước hoa Nước hoa Thu đông 2021
5 3588981242192-1222 Nước hoa TOKYOLIFE UNIVERSE EDP 20ml Nam Hàng tiêu dùng nhanh TOKYOLIFE 20ml 0 Chai Nước hoa Nước hoa Bốn mùa
6 3588981242208-1124 Nước hoa TOKYOLIFE EARTH EDP Nam Hàng tiêu dùng nhanh TOKYOLIFE 24ml 0 Chai Nước hoa Nước hoa Thu đông 2021
7 3588981242208-1222 Nước hoa TOKYOLIFE EARTH EDP 20ml Nam Hàng tiêu dùng nhanh TOKYOLIFE 20ml 0 Chai Nước hoa Nước hoa Bốn mùa
8 3588981242215-1124 Nước hoa TOKYOLIFE MOUNTAIN EDP Nam Hàng tiêu dùng nhanh TOKYOLIFE 24ml 0 Chai Nước hoa Nước hoa Thu đông 2021
9 3588981242215-1222 Nước hoa TOKYOLIFE MOUNTAIN EDP 20ml Nam Hàng tiêu dùng nhanh TOKYOLIFE 20ml 0 Chai Nước hoa Nước hoa Bốn mùa

c: Đẩy dữ liệu hóa đơn vào trong table hóa đơn¶

In [29]:
records_hoadon = df_hoadon.fillna(value=0).values.tolist()
In [51]:
df_hoadon.columns
Out[51]:
Index(['MÃ CN', 'CHI NHÁNH', 'NGÀY HĐ', 'SỐ HĐ', 'MÃ KHÁCH 2', 'MÃ HÀNG',
       'SL BÁN', 'SL TRẢ LẠI', 'GIÁ', 'GIẢM GIÁ', 'G.TRÊN HĐ',
       'THÀNH TIỀN BÁN', 'THÀNH TIỀN NTL', 'MÔ TẢ', 'HẠNG KHÁCH',
       'CHƯƠNG TRÌNH KHUYẾN MẠI', 'SubClass1', 'THUỘC TÍNH 1', 'NĂM SX',
       'MÃ HÀNG1', 'NHÂN VIÊN'],
      dtype='object')
In [38]:
cursor.execute('''
        DROP TABLE IF EXISTS hoadon
        CREATE TABLE hoadon (
        [STT_ID] INT IDENTITY(1,1) PRIMARY KEY,
        [MÃ CN] nvarchar(10),
        [CHI NHÁNH] nvarchar(100),
        [NGÀY HĐ] datetime,
        [SỐ HĐ] nvarchar(50),
        [MÃ KHÁCH 2] int FOREIGN KEY REFERENCES khachhang([MÃ KHÁCH 2]),
        [MÃ HÀNG] nvarchar(50) FOREIGN KEY REFERENCES hanghoa([MÃ HÀNG]),
        [SL BÁN] int,
        [SL TRẢ LẠI] int,
        [GIÁ] int,
        [GIẢM GIÁ] nvarchar(100),
        [G.TRÊN HĐ] nvarchar(100),
        [THÀNH TIỀN BÁN] float,
        [THÀNH TIỀN NTL] float,
        [MÔ TẢ] nvarchar(100),
        [HẠNG KHÁCH] nvarchar(100),
        [CHƯƠNG TRÌNH KHUYẾN MẠI] nvarchar(100),
        [SubClass1] nvarchar(100),
        [THUỘC TÍNH 1] nvarchar(100),
        [NĂM SX] nvarchar(100),
        [MÃ HÀNG1] nvarchar(100),
        [NHÂN VIÊN] nvarchar(50))
               ''')
conn.commit()
In [39]:
sql_insert_hoadon = '''
    INSERT INTO hoadon 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
In [40]:
cursor.executemany(sql_insert_hoadon, records_hoadon)
cursor.commit();
In [41]:
sql_query = """
SELECT TOP 10 *
FROM hoadon
"""
In [42]:
df_check = pd.read_sql(sql_query, conn)
df_check
Out[42]:
STT_ID MÃ CN CHI NHÁNH NGÀY HĐ SỐ HĐ MÃ KHÁCH 2 MÃ HÀNG SL BÁN SL TRẢ LẠI GIÁ ... THÀNH TIỀN BÁN THÀNH TIỀN NTL MÔ TẢ HẠNG KHÁCH CHƯƠNG TRÌNH KHUYẾN MẠI SubClass1 THUỘC TÍNH 1 NĂM SX MÃ HÀNG1 NHÂN VIÊN
0 1 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000175 1160637 I7JCK601F-015 0 1 1290000 ... 0.0 387000.0 0 AppMember 0 cổ 5cm 3 lớp Đông 2019 I7JCK601F 0
1 2 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000176 907256 I9COA502H-005 0 2 790000 ... 0.0 474000.0 0 AppMember 0 có mũ 2 lớp mỏng Đông 2020 I9COA502H 0
2 3 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000177 163424 I7SWT601F-001 0 1 490000 ... 0.0 147000.0 0 AppMember 0 cổ tròn Len Đông 2019 I7SWT601F 0
3 4 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000178 1834386 M9ACP500H-002 0 1 150000 ... 0.0 45000.0 0 AppMember 0 0 0 Đông 2020 M9ACP500H 0
4 5 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000179 1984934 C9SML008D-002 0 1 290000 ... 0.0 87000.0 0 AppMember 0 0 0 Đông 2018 C9SML-008D 0
5 6 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000180 1594687 I9SWS503H-007 0 1 250000 ... 0.0 75000.0 0 AppMember 0 cổ tròn 0 Đông 2020 I9SWS503H 0
6 7 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000181 1831070 E7SHO262H-009 0 1 490000 ... 0.0 343000.0 0 AppMember 0 0 êm chân 2021 E7SHO262H 0
7 8 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000182 1651202 I5JOG501H-009 0 1 190000 ... 0.0 57000.0 0 AppMember 0 0 0 Đông 2020 I5JOG501H 0
8 9 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000182 1651202 I9JOG501H-013 0 1 350000 ... 0.0 105000.0 0 AppMember 0 0 Si chun Đông 2020 I9JOG501H 0
9 10 141 TOKYOLIFE Tam Điệp 2021-01-31 NTL_141/21-000183 1998294 I7PAN500H-005 0 1 350000 ... 0.0 105000.0 0 AppMember 0 0 Si chun Đông 2020 I7PAN500H 0

10 rows × 22 columns

TASK2 : Thực hiện 1 số phân tích cơ bản¶

A. Phân tích với các mặt hàng¶

In [4]:
sql_query = """
SELECT hanghoa.[MÃ HÀNG],[TÊN HÀNG],[GIỚI TÍNH],[NHÓM HÀNG],[DÒNG HÀNG],[KẾT CẤU],[NGÀY HĐ],[SỐ HĐ],[SL BÁN],[SL TRẢ LẠI],[THÀNH TIỀN BÁN]
FROM hanghoa JOIN hoadon
ON hanghoa.[MÃ HÀNG] = hoadon.[MÃ HÀNG]
"""

df_EDA = pd.read_sql(sql_query, conn)
df_EDA
Out[4]:
MÃ HÀNG TÊN HÀNG GIỚI TÍNH NHÓM HÀNG DÒNG HÀNG KẾT CẤU NGÀY HĐ SỐ HĐ SL BÁN SL TRẢ LẠI THÀNH TIỀN BÁN
0 I7JCK601F-015 Áo khoác lông vũ Nam 3 lớp cổ 5cm dáng ngắn I7... Nam Thời trang BASIC Khoác ngoài Áo khoác lông vũ 2021-01-31 NTL_141/21-000175 0 1 0.0
1 I9COA502H-005 Áo khoác Nữ dáng dài I9COA502H Nữ Thời trang BASIC Phần trên Áo khoác 2021-01-31 NTL_141/21-000176 0 2 0.0
2 I7SWT601F-001 Áo len Nam cổ tròn I7SWT601F Nam Thời trang BASIC Len sợi Áo len 2021-01-31 NTL_141/21-000177 0 1 0.0
3 M9ACP500H-002 Quần Leggings lửng Nữ M9ACP500H Nữ Thời trang BASIC Đồ tập Quần Leggings 2021-01-31 NTL_141/21-000178 0 1 0.0
4 C9SML008D-002 Quần Leggings Nữ C9SML-008D Nữ Thời trang BASIC Phần dưới Quần Leggings 2021-01-31 NTL_141/21-000179 0 1 0.0
... ... ... ... ... ... ... ... ... ... ... ...
115045 G9SMT007J-002 Áo giữ nhiệt WM Original Nữ cổ lọ G9SMT007J Nữ Thời trang SMART Warmmax Áo giữ nhiệt 2021-12-01 XBL_141/21-015685 2 0 318000.0
115046 E7JEA001H-001 Quần Jeans Nam E7JEA001H Nam Thời trang NOW Phần dưới Quần Jeans 2021-12-01 XBL_141/21-015686 2 0 590000.0
115047 N7BBE005J-002 Áo Bomber Nam N7BBE005J Nam Thời trang NOW Khoác ngoài Áo Bomber 2021-12-01 XBL_141/21-015687 2 0 1666000.0
115048 N7SWS014J-010 Áo nỉ chui đầu Nam cổ tròn N7SWS014J Nam Thời trang NOW Phần trên Áo nỉ chui đầu 2021-12-01 XBL_141/21-015687 2 0 966000.0
115049 I9TSH602J-005 Áo T-shirt Nữ cổ tròn I9TSH602J Nữ Thời trang BASIC Phần trên Áo T-Shirt 2021-12-01 XBL_141/21-015688 2 0 158000.0

115050 rows × 11 columns

Loại bỏ nhóm hàng 'Túi bán hàng' ra khỏi dữ liệu¶

In [5]:
check = ['Túi bán hàng']

df_EDA = df_EDA[~df_EDA['NHÓM HÀNG'].isin(check)]

Vẽ biểu đồ của top 10 tên hàng, nhóm hàng, dòng hàng, kết cấu theo số lượng bán¶

In [6]:
df_EDA['Month'] = df_EDA['NGÀY HĐ'].dt.month 
df_EDA['Year'] = df_EDA['NGÀY HĐ'].dt.year
df_EDA['Day'] = df_EDA['NGÀY HĐ'].dt.day
df_EDA
Out[6]:
MÃ HÀNG TÊN HÀNG GIỚI TÍNH NHÓM HÀNG DÒNG HÀNG KẾT CẤU NGÀY HĐ SỐ HĐ SL BÁN SL TRẢ LẠI THÀNH TIỀN BÁN Month Year Day
0 I7JCK601F-015 Áo khoác lông vũ Nam 3 lớp cổ 5cm dáng ngắn I7... Nam Thời trang BASIC Khoác ngoài Áo khoác lông vũ 2021-01-31 NTL_141/21-000175 0 1 0.0 1 2021 31
1 I9COA502H-005 Áo khoác Nữ dáng dài I9COA502H Nữ Thời trang BASIC Phần trên Áo khoác 2021-01-31 NTL_141/21-000176 0 2 0.0 1 2021 31
2 I7SWT601F-001 Áo len Nam cổ tròn I7SWT601F Nam Thời trang BASIC Len sợi Áo len 2021-01-31 NTL_141/21-000177 0 1 0.0 1 2021 31
3 M9ACP500H-002 Quần Leggings lửng Nữ M9ACP500H Nữ Thời trang BASIC Đồ tập Quần Leggings 2021-01-31 NTL_141/21-000178 0 1 0.0 1 2021 31
4 C9SML008D-002 Quần Leggings Nữ C9SML-008D Nữ Thời trang BASIC Phần dưới Quần Leggings 2021-01-31 NTL_141/21-000179 0 1 0.0 1 2021 31
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
115045 G9SMT007J-002 Áo giữ nhiệt WM Original Nữ cổ lọ G9SMT007J Nữ Thời trang SMART Warmmax Áo giữ nhiệt 2021-12-01 XBL_141/21-015685 2 0 318000.0 12 2021 1
115046 E7JEA001H-001 Quần Jeans Nam E7JEA001H Nam Thời trang NOW Phần dưới Quần Jeans 2021-12-01 XBL_141/21-015686 2 0 590000.0 12 2021 1
115047 N7BBE005J-002 Áo Bomber Nam N7BBE005J Nam Thời trang NOW Khoác ngoài Áo Bomber 2021-12-01 XBL_141/21-015687 2 0 1666000.0 12 2021 1
115048 N7SWS014J-010 Áo nỉ chui đầu Nam cổ tròn N7SWS014J Nam Thời trang NOW Phần trên Áo nỉ chui đầu 2021-12-01 XBL_141/21-015687 2 0 966000.0 12 2021 1
115049 I9TSH602J-005 Áo T-shirt Nữ cổ tròn I9TSH602J Nữ Thời trang BASIC Phần trên Áo T-Shirt 2021-12-01 XBL_141/21-015688 2 0 158000.0 12 2021 1

110722 rows × 14 columns

In [7]:
app = JupyterDash(__name__)

app.layout = html.Div([
    html.Div([
        html.Div([
            dcc.Dropdown(
            options=[2020,2021,2022],
            value=2022,
            id='Year-check'
            )],style={'width': '48%','float': 'right', 'display': 'block'}),
        html.Div([
            dcc.Dropdown(
            options=[1,2,3,4,5,6,7,8,9,10,11,12],
            value=1,
            id='Month-slider'
            )],style={'width': '48%', 'display': 'block'}),
    ]),

    dcc.Graph(id='graph-with-slider'),

])


@app.callback(
    Output('graph-with-slider', 'figure'),
    Input('Month-slider', 'value'),
    Input('Year-check', 'value'))
def update_figure(selected_month,Year_check):

    df_gioitinh = df_EDA[(df_EDA.Month == selected_month)&(df_EDA.Year == Year_check)].groupby(by='GIỚI TÍNH')['THÀNH TIỀN BÁN'].sum().sort_values(ascending=False)
    df_nhomhang = df_EDA[(df_EDA.Month == selected_month)&(df_EDA.Year == Year_check)].groupby(by='NHÓM HÀNG')['THÀNH TIỀN BÁN'].sum().sort_values(ascending=False).head(5)
    df_ketcau = df_EDA[(df_EDA.Month == selected_month)&(df_EDA.Year == Year_check)].groupby(by='KẾT CẤU')['THÀNH TIỀN BÁN'].sum().sort_values(ascending=False).head(10)
    df_doanhthu = df_EDA[(df_EDA.Month == selected_month)&(df_EDA.Year == Year_check)].groupby(by='NGÀY HĐ')['THÀNH TIỀN BÁN'].sum().sort_values(ascending=False)
    
    fig = make_subplots(rows=2, cols=2,specs=[[{"type": "pie"}, {"type": "pie"}],[{"type": "bar"},{"type": "bar"}]],vertical_spacing = 0.15,subplot_titles=("Tỉ trọng giới tính","Tỉ trọng kết cấu", "Top kết cấu",'Doanh thu'))
    
    
    fig.add_trace(go.Pie(values=df_gioitinh.values,labels=df_gioitinh.index),row=1, col=1)
    
    fig.add_trace(go.Pie(values=df_nhomhang.values,labels=df_nhomhang.index),row=1, col=2)
    
    fig.add_trace(go.Bar(y=df_ketcau.values,x=df_ketcau.index),row=2, col=1)
    
    fig.add_trace(go.Bar(y=df_doanhthu.values,x=df_doanhthu.index),row=2, col=2)
    

    fig.update_layout(width=950, height=600)
    #fig.update_layout(paper_bgcolor="#cdbda3")
    fig.update_xaxes(showticklabels=False)
    fig.update(layout_showlegend=False)


    return fig


if __name__ == '__main__':
    app.run_server(mode='inline')

Dùng table để xem được doanh thu kết cấu hôm nay và các ngày trong quá khứ¶

In [8]:
app = JupyterDash(__name__)

app.layout = html.Div([
    html.Div([
        html.Div([
            dcc.Dropdown(
            options=[i for i in range(1,32)],
            value=5,
            id='Day-check'
            )],style={'width': '34%','float': 'left', 'display': 'inline-block'}),      
        html.Div([
            dcc.Dropdown(
            options=[2020,2021,2022],
            value=2022,
            id='Year-check'
            )],style={'width': '34%','float': 'right', 'display': 'inline-block'}),
        html.Div([
            dcc.Dropdown(
            options=[1,2,3,4,5,6,7,8,9,10,11,12],
            value=5,
            id='Month-slider'
            )],style={'width': '32%', 'display': 'inline-block'}),
    ]),

    dcc.Graph(id='graph-with-slider'),

])


@app.callback(
    Output('graph-with-slider', 'figure'),
    Input('Day-check', 'value'),
    Input('Month-slider', 'value'),
    Input('Year-check', 'value'))
def update_figure(Day_check,selected_month,Year_check):

    # Xử lý tạo tabel doanh thu top kết cấu
    s_today = df_EDA[(df_EDA.Day == Day_check)&(df_EDA.Month == selected_month)&(df_EDA.Year == Year_check)].groupby(by='KẾT CẤU')['THÀNH TIỀN BÁN'].sum().sort_values(ascending=False).head(10)
    s_yesday = df_EDA[(df_EDA.Day == (Day_check-1))&(df_EDA.Month == selected_month)&(df_EDA.Year == Year_check)].groupby(by='KẾT CẤU')['THÀNH TIỀN BÁN'].sum().sort_values(ascending=False).head(10)
    s_monthday = df_EDA[(df_EDA.Day == Day_check)&(df_EDA.Month == (selected_month-1))&(df_EDA.Year == Year_check)].groupby(by='KẾT CẤU')['THÀNH TIỀN BÁN'].sum().sort_values(ascending=False).head(10)
    
    s_kc = pd.concat([s_today,s_yesday,s_monthday],axis=1).head(10)
    s_kc['Kết cấu'] = s_kc.index
    s_kc.columns = ['Hôm nay','Hôm qua','Tháng trước','Kết cấu']
    
    
    fig = go.Figure(data =[go.Table(header=dict(values=['Kết cấu','Hôm nay','Hôm qua','Tháng trước'],
                                       fill_color='paleturquoise',
                                       align='left'),
                            cells=dict(values=[s_kc['Kết cấu'],s_kc['Hôm nay'],s_kc['Hôm qua'],s_kc['Tháng trước']],
                                       fill_color='lavender',
                                       align='left'))])

    fig.update_layout(width=950, height=600)
    fig.update_xaxes(showticklabels=False)
    fig.update(layout_showlegend=False)

    return fig

if __name__ == '__main__':
    app.run_server(mode='inline')

Xem qua biểu đồ tăng trưởng doanh thu¶

In [9]:
app = JupyterDash(__name__)

app.layout = html.Div([
    html.Div([
        html.Div([
            dcc.Dropdown(
            options=[2020,2021,2022],
            value=2022,
            id='Year-check'
            )],style={'width': '48%','float': 'right', 'display': 'block'}),
        html.Div([
            dcc.Dropdown(
            options=[1,2,3,4,5,6,7,8,9,10,11,12],
            value=6,
            id='Month-slider'
            )],style={'width': '48%', 'display': 'block'}),
    ]),

    dcc.Graph(id='graph-with-slider'),

])


@app.callback(
    Output('graph-with-slider', 'figure'),
    Input('Month-slider', 'value'),
    Input('Year-check', 'value'))
def update_figure(selected_month,Year_check):

    df_ketcau = df_EDA[(df_EDA.Month == selected_month)&(df_EDA.Year == Year_check)].groupby(by='NGÀY HĐ')['THÀNH TIỀN BÁN'].sum().head(10)
    df_ketcau = df_ketcau.sort_index(ascending=True)
    
    df_lastketcau = df_EDA[(df_EDA.Month == selected_month)&(df_EDA.Year == (Year_check-1))].groupby(by='NGÀY HĐ')['THÀNH TIỀN BÁN'].sum()
    df_lastketcau = df_lastketcau.sort_index(ascending=True)
    
    fig = px.line(df_ketcau,x=df_ketcau.index,y=df_ketcau.values)
    fig.add_scatter(x=df_ketcau.index,y=df_lastketcau.values,name = 'Last')
    
    fig.update_layout(width=950, height=600)
    #fig.update_layout(paper_bgcolor="#cdbda3")
    #fig.update_xaxes(showticklabels=False)
    fig.update(layout_showlegend=True)


    return fig


if __name__ == '__main__':
    app.run_server(mode='inline')

B. Phân tích với khách hàng¶

Phân loại khách hàng theo RFM¶

In [10]:
sql_query = """

SELECT *
FROM hoadon
Where [NGÀY HĐ]>= DATEADD(year, -1,GETDATE())
"""

df_CUS = pd.read_sql(sql_query, conn)
df_CUS
Out[10]:
STT_ID MÃ CN CHI NHÁNH NGÀY HĐ SỐ HĐ MÃ KHÁCH 2 MÃ HÀNG SL BÁN SL TRẢ LẠI GIÁ ... THÀNH TIỀN BÁN THÀNH TIỀN NTL MÔ TẢ HẠNG KHÁCH CHƯƠNG TRÌNH KHUYẾN MẠI SubClass1 THUỘC TÍNH 1 NĂM SX MÃ HÀNG1 NHÂN VIÊN
0 8224 141 TOKYOLIFE Tam Điệp 2022-01-31 NTL_141/22-000192 1622305 H2I7FLJ501H01 0 1 390000 ... 0.0 219000.0 0 AppMember 0 cổ lọ lông cừu nhân tạo Đông 2020 H2/I7FLJ501H 0
1 8225 141 TOKYOLIFE Tam Điệp 2022-01-31 NTL_141/22-000193 2239595 M9SHO223I-012 0 1 890000 ... 0.0 623000.0 0 AppMember 0 0 kháng khuẩn 2021 M9SHO223I 0
2 8226 141 TOKYOLIFE Tam Điệp 2022-01-31 NTL_141/22-000194 2639452 I7FEJ005J-010 0 1 890000 ... 0.0 598996.7 0 Khách Lẻ 0 Cổ cao 2 lớp siêu nhẹ Đông 2021 I7FEJ005J 0
3 8227 141 TOKYOLIFE Tam Điệp 2022-01-31 NTL_141/22-000195 2288075 N3SLI017I-022 0 1 120000 ... 0.0 89000.4 0 AppMember 0 êm chân siêu nhẹ 2021 N3SLI017I 0
4 8228 141 TOKYOLIFE Tam Điệp 2022-01-31 XBL_141/22-003060 1598604 E7GLV038H-001 2 0 120000 ... 144000.0 0.0 0 Khách Lẻ LGB/22-000019 0 0 2021 E7GLV038H 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
47355 115046 141 TOKYOLIFE Tam Điệp 2021-12-01 XBL_141/21-015685 2391030 G9SMT007J-002 2 0 490000 ... 318000.0 0.0 0 AppMember LGB/21-000123 cổ lọ WM Original Đông 2021 G9SMT007J 0
47356 115047 141 TOKYOLIFE Tam Điệp 2021-12-01 XBL_141/21-015686 1960546 E7JEA001H-001 2 0 590000 ... 590000.0 0.0 0 AppMember LGB/21-000124 0 Jean Đông 2020 E7JEA001H 0
47357 115048 141 TOKYOLIFE Tam Điệp 2021-12-01 XBL_141/21-015687 1960546 N7BBE005J-002 2 0 1190000 ... 1666000.0 0.0 0 AppMember LGB/21-000124 0 2 lớp Đông 2021 N7BBE005J 0
47358 115049 141 TOKYOLIFE Tam Điệp 2021-12-01 XBL_141/21-015687 1960546 N7SWS014J-010 2 0 690000 ... 966000.0 0.0 0 AppMember LGB/21-000124 cổ tròn 0 Đông 2021 N7SWS014J 0
47359 115050 141 TOKYOLIFE Tam Điệp 2021-12-01 XBL_141/21-015688 1677251 I9TSH602J-005 2 0 250000 ... 158000.0 0.0 0 AppMember LGB/21-000124 cổ tròn 0 Đông 2021 I9TSH602J 0

47360 rows × 22 columns

Lấy dữ liệu RFM bằng SQL¶

In [11]:
sql_query = """
SELECT hoadon.[MÃ KHÁCH 2],khachhang.[TÊN KHÁCH],MAX([NGÀY HĐ]) AS 'LAST BUY',COUNT(DISTINCT [SỐ HĐ]) AS 'FREQUENCY',SUM ([THÀNH TIỀN BÁN]) AS 'MONETARY'
FROM hoadon JOIN khachhang
ON hoadon.[MÃ KHÁCH 2] = khachhang.[MÃ KHÁCH 2]
Where [NGÀY HĐ]>= DATEADD(year, -1,GETDATE())
GROUP BY hoadon.[MÃ KHÁCH 2],khachhang.[TÊN KHÁCH]
"""

df_RFM = pd.read_sql(sql_query, conn)
df_RFM
Out[11]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY
0 126781 Phạm Nhật Lệ 2022-04-08 2 990000.0
1 127020 Chị Lê Diệu Linh 2021-08-08 1 2494000.0
2 127463 Nv Đỗ Thị Thu Trang 2022-03-16 1 1012000.0
3 128098 Phạm Thanh Thủy 2022-01-31 1 3444000.0
4 128174 Lê Thị Huế 2022-05-08 7 3822000.0
... ... ... ... ... ...
6083 2822366 Phuong Pham 2022-06-16 1 744000.0
6084 2822527 Anh Tưởng 2022-06-16 3 2810000.0
6085 2822614 Anh Linh 2022-06-16 1 630000.0
6086 2822693 Thu Hà 2022-06-16 1 928000.0
6087 2823376 Anh Hội 2022-06-17 1 1336000.0

6088 rows × 5 columns

In [12]:
df_RFM['RECENCY'] = pd.to_datetime("today") - df_RFM['LAST BUY']
df_RFM['RECENCY'] = df_RFM['RECENCY'].dt.days
df_RFM
Out[12]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY
0 126781 Phạm Nhật Lệ 2022-04-08 2 990000.0 101
1 127020 Chị Lê Diệu Linh 2021-08-08 1 2494000.0 344
2 127463 Nv Đỗ Thị Thu Trang 2022-03-16 1 1012000.0 124
3 128098 Phạm Thanh Thủy 2022-01-31 1 3444000.0 168
4 128174 Lê Thị Huế 2022-05-08 7 3822000.0 71
... ... ... ... ... ... ...
6083 2822366 Phuong Pham 2022-06-16 1 744000.0 32
6084 2822527 Anh Tưởng 2022-06-16 3 2810000.0 32
6085 2822614 Anh Linh 2022-06-16 1 630000.0 32
6086 2822693 Thu Hà 2022-06-16 1 928000.0 32
6087 2823376 Anh Hội 2022-06-17 1 1336000.0 31

6088 rows × 6 columns

Loại bỏ 1 số ngoại lệ¶

  1. Khách không cho thông tin: Mã chung cho tất cả khách hàng không cho thông tin khi thanh toán
  2. Khách nước ngoài: Mã chung cho toàn bộ khách nước ngoài
  3. NV - ... : Mã nhân viên tại hệ thống
In [13]:
df_RFM.sort_values(by='FREQUENCY',ascending=False).head(10)
Out[13]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY
1386 1598604 TL TDIE_Khách không cho thông tin 2022-06-17 592 248854000.0 31
2625 1956765 Nguyễn Ngọc Bảo Uyên 2022-04-27 105 33644000.0 82
1161 1567064 NV_Ngô Thị Dần 2022-06-16 81 35820960.0 32
13 134926 Khách nươc ngoài 2022-06-16 69 38176200.0 32
2560 1924581 Ms Phương 2022-06-05 68 25952000.0 43
1734 1655704 Anh Thoan 2022-06-07 59 25632000.0 41
1158 1565374 NV_Vũ Thùy Trang 2022-06-09 54 14210000.0 39
1323 1594230 Ms.phương 2022-06-07 52 40911200.0 41
1160 1567053 NV_Phạm Thị Lan 2022-06-16 50 21382600.0 32
1170 1573533 NV_Đinh Thị Thùy 2022-05-16 42 12545200.0 63

Loại bỏ Mã nhân viên ra khỏi danh sách bằng regex¶

In [14]:
msk = df_RFM['TÊN KHÁCH'].str.contains('(NV_)[A-Za-z]{0}')
df_RFM[msk]
Out[14]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY
1013 1372679 NV_Lê Thị Lành 2021-11-23 3 1220000.0 237
1026 1390692 NV_Nguyễn Nhân Tâm 2021-11-22 1 590000.0 238
1157 1565114 NV_Phạm Thị Hằng 2021-07-25 1 130000.0 358
1158 1565374 NV_Vũ Thùy Trang 2022-06-09 54 14210000.0 39
1160 1567053 NV_Phạm Thị Lan 2022-06-16 50 21382600.0 32
1161 1567064 NV_Ngô Thị Dần 2022-06-16 81 35820960.0 32
1170 1573533 NV_Đinh Thị Thùy 2022-05-16 42 12545200.0 63
1846 1685398 NV_Đỗ Thị Mỹ Hằng 2021-11-23 3 350000.0 237
2563 1926395 NV_Đinh Thị Thu Hà 2021-10-24 1 240000.0 267
2905 2090698 NV_Lại Thị Thùy Linh 2022-05-01 9 1934000.0 78

Loại bỏ khách không cho thông tin và khách nước ngoài¶

In [15]:
df_RFM = df_RFM[~msk &(df_RFM['TÊN KHÁCH']!='TL TDIE_Khách không cho thông tin')&(df_RFM['TÊN KHÁCH']!='Khách nươc ngoài')]

Quy tắc phân loại khách hàng¶

  1. Chúng ta có 3 tiêu chí phân loại dựa vào hành vi của khách hàng là FREQUENCY, RECENCY, và MONETARY
  2. Mỗi tiêu chí sẽ phân loại theo 3 cấp độ 1,2,3. Như vậy sẽ có 27 nhóm hành vi khách hàng
  3. Cố gắng chia tỉ lệ cấp độ theo thang 5,3,2

1. Chia tỉ lệ cho FREQUENCY¶

In [16]:
px.histogram(df_RFM[df_RFM['FREQUENCY']<10],x='FREQUENCY')
In [17]:
labels = [1,2,3]
values = [df_RFM[df_RFM['FREQUENCY']<2]['FREQUENCY'].count(),df_RFM[(df_RFM['FREQUENCY']<5)&(df_RFM['FREQUENCY']>1)]['FREQUENCY'].count(),df_RFM[df_RFM['FREQUENCY']>4]['FREQUENCY'].count()]
fig = px.pie(values=values,names=labels)
fig.show()
In [18]:
df_RFM['CL_FREQUENCY'] = df_RFM['FREQUENCY'].apply(lambda x: 1 if x < 2 else (2 if  x > 1 and x < 5 else 3))
df_RFM
Out[18]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY CL_FREQUENCY
0 126781 Phạm Nhật Lệ 2022-04-08 2 990000.0 101 2
1 127020 Chị Lê Diệu Linh 2021-08-08 1 2494000.0 344 1
2 127463 Nv Đỗ Thị Thu Trang 2022-03-16 1 1012000.0 124 1
3 128098 Phạm Thanh Thủy 2022-01-31 1 3444000.0 168 1
4 128174 Lê Thị Huế 2022-05-08 7 3822000.0 71 3
... ... ... ... ... ... ... ...
6083 2822366 Phuong Pham 2022-06-16 1 744000.0 32 1
6084 2822527 Anh Tưởng 2022-06-16 3 2810000.0 32 2
6085 2822614 Anh Linh 2022-06-16 1 630000.0 32 1
6086 2822693 Thu Hà 2022-06-16 1 928000.0 32 1
6087 2823376 Anh Hội 2022-06-17 1 1336000.0 31 1

6076 rows × 7 columns

2. Chia tỉ lệ cho MONETARY¶

In [19]:
px.histogram(df_RFM[df_RFM['MONETARY']<10000000],x='MONETARY')
In [20]:
labels = [1,2,3]
values = [df_RFM[df_RFM['MONETARY']<1200000]['MONETARY'].count(),df_RFM[(df_RFM['MONETARY']<3000000)&(df_RFM['MONETARY']>1200000)]['MONETARY'].count(),df_RFM[df_RFM['MONETARY']>3000000]['MONETARY'].count()]
fig = px.pie(values=values,names=labels)
fig.show()
In [21]:
df_RFM['CL_MONETARY'] = df_RFM['MONETARY'].apply(lambda x: 1 if x < 1200000 else (2 if  x > 1199000 and x < 3000000 else 3))
df_RFM
Out[21]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY CL_FREQUENCY CL_MONETARY
0 126781 Phạm Nhật Lệ 2022-04-08 2 990000.0 101 2 1
1 127020 Chị Lê Diệu Linh 2021-08-08 1 2494000.0 344 1 2
2 127463 Nv Đỗ Thị Thu Trang 2022-03-16 1 1012000.0 124 1 1
3 128098 Phạm Thanh Thủy 2022-01-31 1 3444000.0 168 1 3
4 128174 Lê Thị Huế 2022-05-08 7 3822000.0 71 3 3
... ... ... ... ... ... ... ... ...
6083 2822366 Phuong Pham 2022-06-16 1 744000.0 32 1 1
6084 2822527 Anh Tưởng 2022-06-16 3 2810000.0 32 2 2
6085 2822614 Anh Linh 2022-06-16 1 630000.0 32 1 1
6086 2822693 Thu Hà 2022-06-16 1 928000.0 32 1 1
6087 2823376 Anh Hội 2022-06-17 1 1336000.0 31 1 2

6076 rows × 8 columns

3. Chia tỉ lệ cho RECENCY¶

In [22]:
px.histogram(df_RFM['RECENCY'],x='RECENCY')
In [23]:
labels = [1,2,3]
values = [df_RFM[df_RFM['RECENCY']<160]['RECENCY'].count(),df_RFM[(df_RFM['RECENCY']<240)&(df_RFM['RECENCY']>160)]['RECENCY'].count(),df_RFM[df_RFM['RECENCY']>240]['RECENCY'].count()]
fig = px.pie(values=values,names=labels)
fig.show()
In [24]:
df_RFM['CL_RECENCY'] = df_RFM['RECENCY'].apply(lambda x: 1 if x < 160 else (2 if  x > 159 and x < 2400 else 3))
df_RFM
Out[24]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY CL_FREQUENCY CL_MONETARY CL_RECENCY
0 126781 Phạm Nhật Lệ 2022-04-08 2 990000.0 101 2 1 1
1 127020 Chị Lê Diệu Linh 2021-08-08 1 2494000.0 344 1 2 2
2 127463 Nv Đỗ Thị Thu Trang 2022-03-16 1 1012000.0 124 1 1 1
3 128098 Phạm Thanh Thủy 2022-01-31 1 3444000.0 168 1 3 2
4 128174 Lê Thị Huế 2022-05-08 7 3822000.0 71 3 3 1
... ... ... ... ... ... ... ... ... ...
6083 2822366 Phuong Pham 2022-06-16 1 744000.0 32 1 1 1
6084 2822527 Anh Tưởng 2022-06-16 3 2810000.0 32 2 2 1
6085 2822614 Anh Linh 2022-06-16 1 630000.0 32 1 1 1
6086 2822693 Thu Hà 2022-06-16 1 928000.0 32 1 1 1
6087 2823376 Anh Hội 2022-06-17 1 1336000.0 31 1 2 1

6076 rows × 9 columns

Như vậy chúng ta đã thực hiện phân cấp xong cho khách hàng. Tùy tiêu chí của chiến dịch, chúng ta sẽ gộp các nhóm theo các tiêu chí khác nhau để chăm sóc¶

Phân loại RFM bằng k-Means¶

In [25]:
df_RFM
Out[25]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY CL_FREQUENCY CL_MONETARY CL_RECENCY
0 126781 Phạm Nhật Lệ 2022-04-08 2 990000.0 101 2 1 1
1 127020 Chị Lê Diệu Linh 2021-08-08 1 2494000.0 344 1 2 2
2 127463 Nv Đỗ Thị Thu Trang 2022-03-16 1 1012000.0 124 1 1 1
3 128098 Phạm Thanh Thủy 2022-01-31 1 3444000.0 168 1 3 2
4 128174 Lê Thị Huế 2022-05-08 7 3822000.0 71 3 3 1
... ... ... ... ... ... ... ... ... ...
6083 2822366 Phuong Pham 2022-06-16 1 744000.0 32 1 1 1
6084 2822527 Anh Tưởng 2022-06-16 3 2810000.0 32 2 2 1
6085 2822614 Anh Linh 2022-06-16 1 630000.0 32 1 1 1
6086 2822693 Thu Hà 2022-06-16 1 928000.0 32 1 1 1
6087 2823376 Anh Hội 2022-06-17 1 1336000.0 31 1 2 1

6076 rows × 9 columns

In [26]:
from sklearn.preprocessing import StandardScaler
X = df_RFM[['FREQUENCY','MONETARY','RECENCY']].values[:,:]
Clus_dataSet = StandardScaler().fit_transform(X)
pd.DataFrame(Clus_dataSet).head()
Out[26]:
0 1 2
0 -0.202661 -0.376700 -0.760288
1 -0.471988 0.074970 2.215395
2 -0.471988 -0.370093 -0.478639
3 -0.471988 0.360268 0.060168
4 1.143978 0.473786 -1.127656
In [27]:
# Chọn số cụm bằng Elbow
import seaborn as sns
from sklearn.cluster import KMeans

sse = {}

for k in range (1,27):
    k_means = KMeans(init = "k-means++", n_clusters = k, n_init = 12)
    k_means.fit(Clus_dataSet)
    sse[k] = k_means.inertia_
    
plt.xlabel('k')
plt.ylabel('sse')
sns.pointplot(x=list(sse.keys()),y=list(sse.values()))
plt.show()
In [28]:
model =  KMeans(init = "k-means++", n_clusters = 9, n_init = 12)
model.fit(Clus_dataSet)
Out[28]:
KMeans(n_clusters=9, n_init=12)
In [29]:
df_RFM['Label'] = model.labels_
df_RFM
Out[29]:
MÃ KHÁCH 2 TÊN KHÁCH LAST BUY FREQUENCY MONETARY RECENCY CL_FREQUENCY CL_MONETARY CL_RECENCY Label
0 126781 Phạm Nhật Lệ 2022-04-08 2 990000.0 101 2 1 1 5
1 127020 Chị Lê Diệu Linh 2021-08-08 1 2494000.0 344 1 2 2 8
2 127463 Nv Đỗ Thị Thu Trang 2022-03-16 1 1012000.0 124 1 1 1 1
3 128098 Phạm Thanh Thủy 2022-01-31 1 3444000.0 168 1 3 2 1
4 128174 Lê Thị Huế 2022-05-08 7 3822000.0 71 3 3 1 2
... ... ... ... ... ... ... ... ... ... ...
6083 2822366 Phuong Pham 2022-06-16 1 744000.0 32 1 1 1 5
6084 2822527 Anh Tưởng 2022-06-16 3 2810000.0 32 2 2 1 5
6085 2822614 Anh Linh 2022-06-16 1 630000.0 32 1 1 1 5
6086 2822693 Thu Hà 2022-06-16 1 928000.0 32 1 1 1 5
6087 2823376 Anh Hội 2022-06-17 1 1336000.0 31 1 2 1 5

6076 rows × 10 columns

In [30]:
df_RFM.groupby('Label').agg(
{
    'MONETARY':'mean',
    'FREQUENCY':'mean',
    'RECENCY':'mean'
}
).round(2)
Out[30]:
MONETARY FREQUENCY RECENCY
Label
0 3776010.25 4.10 171.94
1 930922.71 1.42 157.01
2 5946333.47 7.16 77.05
3 982696.08 1.41 222.24
4 27755279.93 24.33 57.57
5 1549752.91 1.97 69.81
6 31534800.00 71.00 51.75
7 12756632.32 13.20 79.87
8 927447.96 1.38 311.83

K-Means tự phân nhóm các điểm gần nhau vào 1 cụm. Vì vậy chúng ta cần tự đánh giá dựa theo các tiêu chí trung bình. Có thể đưa ra 1 vài nhận xét về các cụm như sau¶

  1. Cụm 0 bao gồm các khách hàng mua trung bình, thường mua theo mùa
  2. Cụm 2,4,6,7 là những cụm khác nhau nhưng khách hàng có hành vi khá giống nhau. Mua nhiều mua thường xuyên
  3. Cụm 3,8 là những khách hàng vãng lai, mua ít và gần như đã rớt
In [9]:
# Nguồn tham khảo 
#https://docs.microsoft.com/en-us/sql/machine-learning/data-exploration/python-dataframe-pandas?view=sql-server-ver16
#https://github.com/mkleehammer/pyodbc/wiki/Getting-started
#https://medium.com/plotly/introducing-jupyterdash-811f1f57c02e